Q: Way to understand functions/methods?
Ans: we must know 3 things:
(i) What it does?
(ii) What param/input it accepts 
(iii) What output/return type it provides
------------------------------------------------------------------------
LOWER: It is used to convert the input into lower case

params:
LOWER(string)

return type:
string

Ex:select LOWER('Welcome TO SG TESTING institute') as RESULT from dual;

RESULT
-------------------------------
welcome to sg testing institute
##################################################################
UPPER: It is used to convert the given input into upper case.

params:
UPPER(string)

return type:
string

Ex:
select UPPER('good Morning') as result from dual;
##################################################################
INITCAP: It will convert the first letter of every word into upper case (camel Case)

Params:
INITCAP(string)

return Type:
string

Ex:
select INITCAP('welcome to sg testing institute') as result from dual;
##################################################################
LENGTH: It will display the count of number of characters present in the given string.

Params:
LENGTH(String)

Return Type:
number

Ex:
select LENGTH('Good Day @1234 ') as result from dual;
##################################################################
SUBSTR: it is used to extract character/s from the given input.

Params:
SUBSTR(string, start, length)

Return Type:
string

Ex:
select SUBSTR('Good morning',5, 7) as result from dual; 
##################################################################
INSTR: it is used to search the values in the given string.

Params:
INSTR(string, search)
INSTR(string, search, start, occurrence)

Return Type:
number
0 means search has not found
>0 means search has found. The number indicates search position.

Ex:
select INSTR('SG Testing SG Testing SG Testing', 'SG') as result from dual;

select INSTR('SG Testing SG Testing SG Testing', 'SG', 1, 2) as result from dual;

    RESULT
----------
        12
##################################################################
LTRIM: It is used to remove the left side spaces/characters from the given string.

Params:
LTRIM(string)
LTRIM(string, characters to remove)

Return Type:
String

Ex:
select LTRIM('    SG TESTING    ') as result from dual;

select LTRIM('good morning', 'dog') as result from dual;
##################################################################

RTRIM: It is used to remove the right side spaces/characters from the given string.

Params:
RTRIM(string)
RTRIM(string, characters to remove)

Return Type:
String

Ex:
select RTRIM('    SG TESTING    ') as result from dual;

RESULT
--------------
    SG TESTING

select RTRIM('good morning', 'morning') as result from dual;

RESUL
-----
good

##################################################################

TRIM: It will remove spaces/characters from both left and right side of the given string.

Params:
TRIM (leading <char> from <String>)
TRIM (trailing <char> from <String>)
TRIM (both <char> from <String>)

Return Type:
String

Ex:
select TRIM(leading '*' from '*****SG TEST*****') as result from dual;

RESULT
------------
SG TEST*****

SQL>
SQL> select TRIM(trailing '*' from '*****SG TEST*****') as result from dual;

RESULT
------------
*****SG TEST

SQL> select TRIM(both '*' from '*****SG TEST*****') as result from dual;

RESULT
-------
SG TEST

##################################################################
REPLACE: It is used to replace the character/s with sequential set of data. It will replace all the matches.

Params:
REPLACE(string, find, replacement)

Return Type:
String

Ex:
SQL> select REPLACE('good morning', 'good', 'BEST') as result from dual;

RESULT
------------
BEST morning
##################################################################

TRANSLATE: It is used to replace the character/s with non-sequential set of data. It will replace all the matches.

Params:
TRANSLATE(string, find, replacement)

Return Type:
String

Ex:
select TRANSLATE('ABCDEFGH', 'ADH', '@#$') as result from dual;

RESULT
--------
@BC#EFG$
##################################################################

LPAD: It is used to fill the values in the left side of the given string based on the length specified.

Params:
LPAD(string, length, value to pad)

Return Type:
String

Ex:
select LPAD('SQLPLUS', 10, '@') as result from dual;

RESULT
----------
@@@SQLPLUS

SQL> select LPAD('SQLPLUS', 7, '@') as result from dual;

RESULT
-------
SQLPLUS

SQL> select LPAD('SQLPLUS', 3, '@') as result from dual;

RES
---
SQL
##################################################################
RPAD: It is used to fill the values in the Right side of the given string based on the length specified.

Params:
RPAD(string, length, value to pad)

Return Type:
String

Ex:
select RPAD('SQLPLUS', 10, '@') as result from dual;

RESULT
----------
SQLPLUS@@@

SQL> select RPAD('SQLPLUS', 7, '@') as result from dual;

RESULT
-------
SQLPLUS

SQL> select RPAD('SQLPLUS', 3, '@') as result from dual;

RES
---
SQL
##################################################################
ASCII: It will give the numberical representation of the given character.

Params:
ASCII(character)

return Type:
number

Ex:
select ASCII('A') as result from dual;

    RESULT
----------
        65

SQL> select ASCII('?') as result from dual;

    RESULT
----------
        63
##################################################################

CHR: It will give the character representation of the given number.

Params:
CHR(number)

return Type:
character

Ex:
select CHR(65) as reult from dual;
R
-
A

select CHR(63) as reult from dual;
R
-
?




